﻿
''=============================
'' 提供秒相对应的服务接口
'' 添加人：李大龙
'' 日期：2010-11-30
''=============================
Imports MySql.Data.MySqlClient
Imports TBCC.UtilityMobile
''' <summary>
''' 邮件服务接口
''' </summary>
''' <remarks></remarks>
Public Class CarMailService

    ''' <summary>
    ''' 保持Mailto
    ''' </summary>
    ''' <param name="mailto"></param>
    ''' <remarks></remarks>
    Public Shared Sub SaveMailTo(ByVal mailto As MailTo)

        Dim strsql As String = "INSERT INTO tbcccarmail_to (ProjectID,MailTo) values (?p0,?p1)"
        Dim p(1) As MySql.Data.MySqlClient.MySqlParameter
        For i As Integer = 0 To p.GetUpperBound(0)
            p(i) = New MySql.Data.MySqlClient.MySqlParameter
            p(i).ParameterName = "p" + i.ToString
        Next

        p(0).Value = mailto.ProjectId
        p(1).Value = mailto.TO
        SqlHelper.ExecuteNonQuery(strsql, p)
    End Sub

    ''' <summary>
    ''' 移除指定的MailTo对象
    ''' </summary>
    ''' <param name="projectId">移除指定的projectId对象</param>
    ''' <remarks></remarks>
    Public Shared Sub RemoveMailTo(ByVal projectId As String)
        Dim strsql As String = "Delete From tbcccarmail_to Where ProjectID= '" + projectId + "'"
        
        SqlHelper.ExecuteNonQuery(strsql, Nothing)
    End Sub

    ''' <summary>
    ''' 保存MailFrom
    ''' </summary>
    ''' <param name="mailfrom">要保存的MailFrom对象</param>
    ''' <remarks></remarks>
    Public Shared Sub SaveMailFrom(ByVal mailfrom As MailFrom)
        Dim tic As New TBCC.UtilityMobile.TranstrationItem
        '' 先删除原有，在插入
        Dim strsql As String = String.Format("delete  FROM tbcccarmail_from WHERE tbcccarmail_from.ProjectID  = '{0}'", mailfrom.ProjectId)
        tic.Add(strsql, Nothing)



        strsql = "INSERT INTO tbcccarmail_from (ProjectID,MailFrom,`Password`,`Smtp`,`Subject`,`Body`) values (?p0,?p1,?p2,?p3,?p4,?p5)"
        Dim p(5) As MySql.Data.MySqlClient.MySqlParameter
        For i As Integer = 0 To p.GetUpperBound(0)
            p(i) = New MySql.Data.MySqlClient.MySqlParameter
            p(i).ParameterName = "p" + i.ToString
        Next

        p(0).Value = mailfrom.ProjectId
        p(1).Value = mailfrom.Account
        p(2).Value = mailfrom.Password
        p(3).Value = mailfrom.SMTP
        p(4).Value = mailfrom.Subject
        p(5).Value = mailfrom.Body

        tic.Add(strsql, p)

        SqlHelper.ExecuteTranstration(tic)
    End Sub


    ''' <summary>
    ''' 移除指定的Mailfrom对象
    ''' </summary>
    ''' <param name="mailfrom">移除指定的Mailfrom对象</param>
    ''' <remarks></remarks>
    Public Shared Sub RemoveMailFrom(ByVal mailfrom As MailFrom)
        Dim strsql As String = String.Format("Delete From tbcccarmail_from Where tbcccarmail_from.ProjectID  = '{0}'", mailfrom.ProjectId)
        SqlHelper.ExecuteNonQuery(strsql, Nothing)
    End Sub

    ''' <summary>
    ''' 移除指定的ProjectId对象
    ''' </summary>
    ''' <param name="ProjectId">移除指定的ProjectId对象</param>
    ''' <remarks></remarks>
    Public Shared Sub RemoveMailFrom(ByVal ProjectId As String)
        Dim strsql As String = String.Format("Delete From tbcccarmail_from Where tbcccarmail_from.ProjectID  = '{0}'", ProjectId)
        SqlHelper.ExecuteNonQuery(strsql, Nothing)
    End Sub

    ''' <summary>
    ''' 获取指定项目的MailTo
    ''' </summary>
    ''' <param name="projectId">要获取的项目编号</param>
    ''' <returns>返回该项目的MialTo数组</returns>
    ''' <remarks></remarks>
    Public Shared Function GetMailTo(ByVal projectId As String) As List(Of MailTo)
        Dim list As New List(Of MailTo)
        Dim strSql = String.Format("SELECT MailTo FROM tbcccarmail_to WHERE ProjectID = '{0}'", projectId)

        Dim dt As DataSet
        dt = SqlHelper.ExecuteDataset(strSql, Nothing)

        If dt.Tables IsNot Nothing Then
            If dt.Tables.Count = 1 Then
                For Each r As DataRow In dt.Tables(0).Rows
                    Dim m As New MailTo
                    m.ProjectId = projectId
                    m.TO = SqlHelper.GetDbData(r("MailTo"))
                    list.Add(m)
                Next
            End If
        End If

        Return list
    End Function

    ''' <summary>
    ''' 获取指定项目的MailFrom
    ''' </summary>
    ''' <param name="projectId">要获取的项目编号</param>
    ''' <returns>返回该项目的MailFrom</returns>
    ''' <remarks></remarks>
    Public Shared Function GetMailFrom(ByVal projectId As String) As MailFrom
        Dim strSql = String.Format("SELECT ProjectID,MailFrom,`Password`,`Smtp`,`Subject`,`Body` FROM tbcccarmail_from WHERE tbcccarmail_from.ProjectID  = '{0}'", projectId)

        Dim dt As DataSet
        dt = SqlHelper.ExecuteDataset(strSql, Nothing)
        If dt.Tables IsNot Nothing Then
            If dt.Tables.Count = 1 Then
                If dt.Tables(0).Rows.Count > 0 Then
                    Dim r = dt.Tables(0).Rows(0)
                    Dim mf As New MailFrom()
                    mf.ProjectId = SqlHelper.GetDbData(r("ProjectID"))
                    mf.Account = SqlHelper.GetDbData(r("MailFrom"))
                    mf.Password = SqlHelper.GetDbData(r("Password"))
                    mf.SMTP = SqlHelper.GetDbData(r("Smtp"))
                    mf.Subject = SqlHelper.GetDbData(r("Subject"))
                    mf.Body = SqlHelper.GetDbData(r("Body"))
                    Return mf
                End If
            End If
        End If
        Return Nothing
    End Function


    ''' <summary>
    ''' 获取项目需要发送的起停记录
    ''' </summary>
    ''' <param name="projectId">项目编号</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetUnMailSent(ByVal projectId As String) As List(Of ProjectMailSentStatus)
        ''=======================================
        '' 历史数据更新状态: UpdateStatus
        '' 0:      未上传()
        '' 1:      未完成()
        '' 2:      已完成()
        '' 3:      尚未停止()
        '' Sent:0 未发送 Sent：1 已发送
        Dim strSql As String = String.Format("SELECT Id,BeginTime,EndTime,Sent From tbcchiststartup_{0}_1 where UpdateStatus=2 And Sent=0", projectId)
        Dim lst As New List(Of ProjectMailSentStatus)
        Dim ds As DataSet = SqlHelper.ExecuteDataset(strSql, Nothing)
        If (ds IsNot Nothing AndAlso ds.Tables.Count > 0 AndAlso ds.Tables(0).Rows.Count > 0) Then

            For Each r As DataRow In ds.Tables(0).Rows
                '' =================================
                '' 读取数据
                Dim pms As New ProjectMailSentStatus
                pms.ProjectID = projectId
                pms.ParentId = Convert.ToInt64(r("Id").ToString())
                pms.BeginTime = Convert.ToDateTime(r("BeginTime").ToString())
                pms.EndTime = Convert.ToDateTime(r("EndTime").ToString())
                pms.Sent = Convert.ToInt32(r("Sent").ToString())
                lst.Add(pms)
            Next
        End If
        Return lst
    End Function

    ''' <summary>
    ''' 更新邮件发送状态
    ''' </summary>
    ''' <param name="id"></param>
    ''' <remarks></remarks>
    Public Shared Sub SentMailFinished(ByVal projectId As String, ByVal id As Integer)
        Dim strSql As String = String.Format("UPDATE  tbcchiststartup_{0}_1 set Sent=1  where Id={1}", projectId, id)
        SqlHelper.ExecuteNonQuery(strSql, Nothing)
    End Sub

    ''' <summary>
    ''' 返回指定项目的特定起停的历史数据
    ''' </summary>
    ''' <param name="projectId"></param>
    ''' <param name="Id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetHistData(ByVal projectId As String, ByVal Id As Integer) As DataSet
        Dim strsql As String = String.Format("SELECT `UpdateTime`,`AI1`,`AI2`,`AI3`,`AI4`,`AlarmStatus`  FROM tbcchistdata_car_{0}_1 WHERE ParentId={1}", projectId, Id)
        Return SqlHelper.ExecuteDataset(strsql, Nothing)
    End Function
End Class

Public Class ProjectMailSentStatus
    Public ProjectID As String
    Public ParentId As Long
    Public BeginTime As DateTime
    Public EndTime As DateTime
    Public Sent As Integer
End Class